#!/usr/bin/perl
use cPanelUserConfig;
use strict;
use XML::Parser;
use CGI;
use Encode qw(from_to encode decode);
use sys;
use Digest::CRC "crc16";
use Time::HiRes qw( gettimeofday );
use Data::Dumper;

$| = 1;

my $cgi = new CGI;
print $cgi->header;

print qq(
<!doctype html public "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Database parser</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf8">
</head>
<pre>
);

my %round_key = (
    "FIN"   => 1000000, 
    "F"     => 1000001,
    "S"     => 900000,
    "SF"    => 900001,
    "Q"     => 800000,
    "QF"    => 800001,
    );

my $input_file = sys::get_input_file();

print "input file: $input_file\n";

open F, ">flag.txt" or sys::err("unable to open flag.txt");
close F;

my $log_id = 0;

my $query = "";
my %db_pusher = ();
my $db_pusher_count = 5000;

unlink "parse_errors.log";      # delete parse log

my $accum = 0;
my @t1 = ();
my @t2 = ();

# load unrated games
my %unrated = ();
if (open F, "<unrated.txt")
{
    while(<F>)
    {
        chomp;
        my @a = split/\s*:\s*/;
        $unrated{$a[0]} = 1;
    }
}

my $parse_ok = 0;

my %tokens;
my $parser = new XML::Parser(Handlers => {Start => \&handle_start, End => \&handle_end, Char => \&handle_char});

print "parsing xml...";
@t1 = gettimeofday;
$parser->parsefile($input_file, ProtocolEncoding => 'ISO-8859-1');
@t2 = gettimeofday;
accum(@t2,@t1);
show_accum();

$parse_ok = 1;

print "creating tables...";
@t1 = gettimeofday;

$dbh->do("DROP TABLE IF EXISTS " . $table_player);
$dbh->do("DROP TABLE IF EXISTS " . $table_log);
$dbh->do("DROP TABLE IF EXISTS " . $table_rating);
$dbh->do("DROP TABLE IF EXISTS " . $table_tournament);
$dbh->do("DROP TABLE IF EXISTS " . $table_game);
$dbh->do("DROP TABLE IF EXISTS " . $table_country);
$dbh->do("DROP TABLE IF EXISTS " . $table_city);
$dbh->do("DROP TABLE IF EXISTS " . $table_admin);
$dbh->do("DROP TABLE IF EXISTS " . $table_admin_log);

my $charset = "utf8";
my $engine = "InnoDB";

$query = "CREATE TABLE $table_player (
    player_id INT UNSIGNED,
    name VARCHAR(32),
    surname VARCHAR(32),
    country_id INT UNSIGNED,
    city_id INT UNSIGNED,
    performance_string VARCHAR(64),
    PRIMARY KEY(player_id),
    KEY `ix_cnd` (country_id),
    KEY `ix_cid` (city_id)
) ENGINE=$engine DEFAULT CHARSET=$charset COLLATE=$charset" . "_bin";
$dbh->do($query);

$query = "CREATE TABLE $table_log (
    id INT UNSIGNED,
    player_id INT UNSIGNED,
    opponent_id INT UNSIGNED,
    opprating FLOAT,
    game_id INT UNSIGNED,
    tournament_id INT,
    round VARCHAR(32),
    round_key INT,
    oppestablished INT UNSIGNED,
    meestablished INT UNSIGNED,
    expected_score FLOAT,
    actual_score FLOAT,
    k_factor FLOAT,
    PRIMARY KEY(id),
    KEY `ix_pid` (player_id),
    KEY `ix_gid` (game_id),
    KEY `ix_oid` (opponent_id),
    KEY `ix_tid` (tournament_id)
) ENGINE=$engine DEFAULT CHARSET=$charset COLLATE=$charset" . "_bin";
$dbh->do($query);

$query = "CREATE TABLE $table_rating (
    tournament_id INT,
    player_id INT UNSIGNED,
    player_start_rating FLOAT,
    player_end_rating FLOAT,
    player_avg_opp_rating FLOAT,
    player_games INT,
    player_points FLOAT,
    datesec INT UNSIGNED,
    g INT UNSIGNED,
    
    wins MEDIUMINT UNSIGNED,
    losses MEDIUMINT UNSIGNED,
    draws MEDIUMINT UNSIGNED,
    is_established TINYINT UNSIGNED,
    
    KEY `ix_tid` (tournament_id),
    KEY `ix_did` (datesec),
    KEY `ix_pid` (player_id),
    KEY `ix_per` (player_end_rating)
) ENGINE=$engine DEFAULT CHARSET=$charset COLLATE=$charset" . "_bin";
$dbh->do($query);

$query = "CREATE TABLE $table_tournament (
    tournament_id INT,
    tournament_name TEXT,
    city_id INT,
    country_id INT,
    year MEDIUMINT UNSIGNED,
    start_date VARCHAR(32),
    end_date VARCHAR(32),
    is_rated TINYINT UNSIGNED,
    is_processed TINYINT UNSIGNED,
    parse_info VARCHAR(32),
    PRIMARY KEY (tournament_id),
    KEY `ix_cid` (city_id),
    KEY `ix_cnd` (country_id),
    KEY `ix_rtd` (is_rated),
    KEY `ix_pcd` (is_processed),
    KEY `ix_edt` (end_date)
) ENGINE=$engine DEFAULT CHARSET=$charset COLLATE=$charset" . "_bin";
$dbh->do($query);

$query = "CREATE TABLE $table_game (
    game_id INT,
    tournament_id INT,
    black_id INT,
    white_id INT,
    bresult FLOAT,
    PRIMARY KEY (game_id),
    KEY `ix_tid` (tournament_id)

) ENGINE=$engine DEFAULT CHARSET=$charset COLLATE=$charset" . "_bin";
$dbh->do($query);

$query = "CREATE TABLE $table_country (
    country_id INT,
    country_name VARCHAR(32),
    country_abbr VARCHAR(8),
    PRIMARY KEY (country_id)
) ENGINE=$engine DEFAULT CHARSET=$charset COLLATE=$charset" . "_bin";
$dbh->do($query);

$query = "CREATE TABLE $table_city (
    city_id INT,
    country_id INT,
    city_name VARCHAR(32),
    PRIMARY KEY (city_id),
    KEY `ix_cnd` (country_id)
) ENGINE=$engine DEFAULT CHARSET=$charset COLLATE=$charset" . "_bin";
$dbh->do($query);

$query = "CREATE TABLE $table_admin (
    player_id INT UNSIGNED,
    password VARCHAR(38),
    email VARCHAR(32),
    level INT UNSIGNED,
    PRIMARY KEY(player_id)
) ENGINE=$engine DEFAULT CHARSET=$charset COLLATE=$charset" . "_bin";
$dbh->do($query);

$query = "CREATE TABLE $table_admin_log (
    player_id INT UNSIGNED,
    log TEXT,
    KEY `ix_pid` (player_id)
) ENGINE=$engine DEFAULT CHARSET=$charset COLLATE=$charset" . "_bin";
$dbh->do($query);

open F, ">not_configured" or die "write error";
close F;

@t2 = gettimeofday;
accum(@t2,@t1);
show_accum();

sub accum
{
    my $s1 = shift(@_)*1000000 + shift(@_);
    my $s2 = shift(@_)*1000000 + shift(@_);
    $accum += ($s1-$s2);
}

sub show_accum
{
    print int($accum/1000) . "ms\n";
    $accum = 0;
}

open F, ">info.txt";
print F "last update: " . $tokens{database}->[0]->{date} . "<br>";
close F;

my $countries = $tokens{"country"};
my $cities = $tokens{"city"};
my $tournaments = $tokens{"tournament"};
my $players = $tokens{"player"};
my $games = $tokens{"game"};

my %tid;    # tournament map
my %pid;    # player map

sub make_map
{
    my ($hash, $array) = (@_);
    for (0..@$array-1)
    {
        $hash->{$array->[$_]->{id}} = $_;
    }
}

@t1 = gettimeofday;
print "creating maps...";

make_map(\%tid, $tournaments);
make_map(\%pid, $players);

@t2 = gettimeofday;
accum(@t2,@t1);
show_accum();

@t1 = gettimeofday;
print "fixing tournaments...";

# handle 0000-00-00 end tournament dates
for (@$tournaments)
{
    my ($yyyy, $mm, $dd) = ("0000", "00", "00");
    if ($_->{end} =~ /^(\d\d\d\d)-(\d\d)-(\d\d)$/)
    {
        ($yyyy, $mm, $dd) = ($1, $2, $3);
    }
    
    if ($yyyy =~ /^0/ || $mm <= 0 || $mm > 12)
    {
        warning("tournament id=" . $_->{id} . " has undefined end date");
        $_->{end} = $_->{year} . "-12-31";  # set last day of tournament year
        $_->{parse_info} = qq(Undefined 'end' date);
        $_->{rated} = 0;
    }
    elsif ($dd <= 0 || $dd > 31)
    {
        warning("tournament id=" . $_->{id} . " has invalid day: " . $_->{end});
        $_->{parse_info} = qq(Invalid 'end' date: $_->{end});
        $_->{end} = "$yyyy-$mm-01";  # handle dates like 2006-12-00
        $_->{rated} = 0;
    }
    elsif (sys::date_to_sec($_->{end}) > time())
    {
        $_->{rated} = 0;
        $_->{parse_info} = qq(Running, end date: $_->{end});
    }
    elsif (!$_->{rated})
    {
        $_->{parse_info} = qq(Unrated tournament);
    }
    else
    {
        $_->{parse_info} = "";
    }
}

@t2 = gettimeofday;
accum(@t2,@t1);
show_accum();

@t1 = gettimeofday;
print "adding cities and countries...";
for (@$countries)
{
    push_to_db($table_country,
        $_->{id},
        qq("$_->{name}"),
        qq("$_->{abbr}"),
        );
}
for (@$cities)
{
    push_to_db($table_city,
        $_->{id},
        $_->{country},
        qq("$_->{name}"),
        );
}
@t2 = gettimeofday;
accum(@t2,@t1);
show_accum();

@t1 = gettimeofday;
print "attaching games to tournaments...";
# attach games to tournaments
for (@$games)
{
    my $tid = $_->{tournament};
    
    push_to_db($table_game,
        $_->{id},
        $_->{tournament},
        $_->{black},
        $_->{white},
        ($_->{bresult}+0),
        );
   
    if (!defined $tid{$tid})
    {
        warning("tournament id=" . $tid . " is unknown in game id=" . $_->{id});
        next;
    }
    if ($_->{round}+0 == 0)
    {
        if ($_->{round} =~ /(\d+)/)
        {
            my $add = 100;
            my $round = $1;
            my $key = 0;
            if ($_->{round} =~ /^(.*?)(\d+)$/)
            {
                if (defined $round_key{uc($1)})
                {
                    $key = $round_key{uc($1)};
                }
                else
                {
                    $key = crc16(uc($1));
                }
            }
            $_->{round_key} = $add + $round + $key;   # assuming rounds like A1, A2, ... and moving such rounds to the end of tournament
        }
        else
        {
            my $add = 0;
            if (defined $round_key{uc($_->{round})})
            {
                $add = $round_key{uc($_->{round})};
            }
            $_->{round_key} = $add + $_->{id}; # non-numerical round, need something for proper sorting, maybe game id is ok for that
        }
    }
    else
    {
        $_->{round_key} = $_->{round}+0;
    }
    my $t = $tournaments->[$tid{$tid}];
    $t->{games} = [] if !defined $t->{games};
    my $r = $t->{games};
    push @$r, $_;
}

# sort tournaments by end date
@$tournaments = sort
{
    sys::date_to_sec($a->{end}) <=> sys::date_to_sec($b->{end});
}@$tournaments;

# update map tournament id to tournament index
make_map(\%tid, $tournaments);

# initialize players ratings
for (@$players)
{
    $_->{rating} = -1;
    $_->{performance_string} = "";
    $_->{wins} = 0;
    $_->{losses} = 0;
    $_->{draws} = 0;
    $_->{rif_updated} = 0;
    $_->{estab_games} = 0;
    $_->{estab_points} = 0;
    $_->{is_established} = 0;
    
    # non-database fields
    $_->{tournamen_log} = [];             # history of rating changes
    $_->{avg_opp_rating} = 0;   # average rating of established opponents (Ra)
    $_->{g} = 0;    # count of games in particular tournament
}

# load Stepanov's adjustments
my @adjust_info = ();
my @indata_files = ("indata.000", "indata.001");

@t2 = gettimeofday;
accum(@t2,@t1);
show_accum();

@t1 = gettimeofday;
print "loading indata...";

for (@indata_files)
{
    open F, "<$_" or sys::err("failed to open $_");
    my @a = split/\n/, join("", <F>);
    close F;
    my $info = {};
    $info->{date} = shift @a;
    $info->{date_sec} = sys::date_to_sec($info->{date});
    sys::err("adjust date invalid: adjust_date") unless $info->{date} =~ /(\d\d\d\d)-\d\d-\d\d/;
    $info->{year} = $1;
    my %stepanov_rating = ();

    for (@a)
    {
        my @b = split/:/;
        my ($id, $rating, $avg_opp_rating, $games, $points) = ($b[1], $b[2], $b[5], $b[6], $b[7]);
        if (-1 != $id)
        {
            $stepanov_rating{$id} = {};
            $stepanov_rating{$id}->{rating} = $rating;
            if (defined $points)
            {
                $stepanov_rating{$id}->{rating} = 0;
                $stepanov_rating{$id}->{avg_opp_rating} = $avg_opp_rating;
                $stepanov_rating{$id}->{estab_games} = $games;
                $stepanov_rating{$id}->{estab_points} = $points;
                
            }
        }
    }
    $info->{stepanov_rating} = \%stepanov_rating;
    push @adjust_info, $info;
}


@t2 = gettimeofday;
accum(@t2,@t1);
show_accum();

@t1 = gettimeofday;
print "calculating ratings";

my $trn_counter = 0;
my $tid_virtual = -1;

# now iterate tournaments from first to last and compute ratings
for (@$tournaments)
{
    $trn_counter++;
    print "." if ($trn_counter % 50 == 0);
    my $tournament = $_;
    my $tend = sys::date_to_sec($_->{end});
    my $adjusted = 0;
    for (@adjust_info)
    {
        my ($adjust_date, $adjust_date_sec, $adjust_year, $stepanov_rating) = 
            ($_->{date}, $_->{date_sec}, $_->{year}, $_->{stepanov_rating});
        if ($adjust_date_sec && $tend >= $adjust_date_sec)
        {
            # add ajustment to database
            # $adjust_date_sec = 0;
            $adjusted = 1;
            push_to_db($table_tournament, $tid_virtual,
                qq("RIF update $adjust_date"),
                3,
                2,
                $adjust_year,
                qq("$adjust_date"),
                qq("$adjust_date"),
                1,
                1,
                qq(""));
            for (@$players)
            {
                my $p = $_;
                if (defined $stepanov_rating->{$p->{id}})
                {
                    my $sr = $stepanov_rating->{$p->{id}};
                    my $start_rating = $p->{rating};
                    if (defined $sr->{estab_points})
                    {
                        $p->{avg_opp_rating} = $sr->{avg_opp_rating};
                        $p->{estab_games} = $sr->{estab_games};
                        $p->{estab_points} = $sr->{estab_points};
                        $p->{rif_updated} = 0;
                    }
                    else
                    {
                        $p->{rating} = $sr->{rating};
                        $p->{rif_updated} = 1;
                    }
                    $p->{performance_string} = $p->{rating} if $p->{performance_string} eq "";
                    push_to_db($table_log,
                        ++$log_id,
                        $p->{id},
                        $p->{id},
                        $p->{rating},
                        -1,
                        $tid_virtual,
                        qq("n/a"),
                        -1,
                        1,
                        1,
                        1,
                        1,
                        0);
                    $p->{is_established} = $p->{rating} > 0 ? 1 : 0;
                    push_to_db($table_rating, 
                        $tid_virtual, 
                        $p->{id}, 
                        $start_rating, 
                        $p->{rating},
                        $p->{avg_opp_rating},
                        $p->{estab_games},
                        $p->{estab_points},
                        $adjust_date_sec,
                        0,
                        
                        $p->{wins},
                        $p->{losses},
                        $p->{draws},
                        $p->{is_established},
                        );
                        
                }
            }
        }
    }
    if ($adjusted)
    {
        shift @adjust_info;
        $tid_virtual--;
    }
    push_to_db($table_tournament, $_->{id},
        qq("$_->{name}"),
        $_->{city},
        $_->{country},
        $_->{year},
        qq("$_->{start}"),
        qq("$_->{end}"),
        $_->{rated},
        1,
        qq("$_->{parse_info}")
        );
    my $r = $_->{games};
    if (!defined $r)
    {
        warning("tournament id=" . $_->{id} . " has no games");
        next;
    }

    # sort games by round
    @$r = sort
    {
        $b->{round_key} <=> $a->{round_key};
    } @$r;


    # build list of players of tournament
    my %plr_indexes = ();
    for (@$r)
    {
        $plr_indexes{$pid{$_->{black}}}++;
        $plr_indexes{$pid{$_->{white}}}++;
    }

    # reset players' info
    for (sort keys %plr_indexes)
    {
        my $p = $$players[$_];
        $p->{tournament_log} = []; # array of logs
        $p->{start_rating} = $p->{rating};
    }

    # iterate games of tournament and update estimated and actual score of players
    for (@$r)
    {
        my $game = $_;
        my $black = $players->[$pid{$_->{black}}];
        my $white = $players->[$pid{$_->{white}}];
        my $bresult = $game->{bresult};
        next if $bresult eq "";
        calculate_rating($black, $white, $bresult, $game, $tournament->{rated});
        calculate_rating($white, $black, 1-$bresult, $game, $tournament->{rated});
    }

    # update players' info
    for (keys %plr_indexes)
    {
        step_log($_);
        my $p = $players->[$_];
        push_to_db($table_rating, $tournament->{id}, 
            $p->{id}, 
            $p->{start_rating}, 
            $p->{rating},
            $p->{avg_opp_rating},
            $p->{estab_games},
            $p->{estab_points},
            $tend,
            $p->{g},
            $p->{wins},
            $p->{losses},
            $p->{draws},
            $p->{is_established},
            );
        $p->{g} = 0;
    }
}

@t2 = gettimeofday;
accum(@t2,@t1);
show_accum();

@t1 = gettimeofday;
print "updating players...";

# now we have calculated all data, let's output htmls
for (sort{$b->{rating} <=> $a->{rating}} @$players)
{
    push_to_db($table_player, $_->{id}, 
        qq("$_->{name}"),
        qq("$_->{surname}"),
        qq($_->{country}),
        qq($_->{city}),
        qq("$_->{performance_string}"),
        );
}

# final db push
for (keys %db_pusher)
{
    flush_push($_);
}

unlink("flag.txt");

@t2 = gettimeofday;
accum(@t2,@t1);
show_accum();

print "finished!\n";

sub push_to_db
{
    my $table = shift(@_);
    $db_pusher{$table} = [] if !defined $db_pusher{$table};
    my $r = $db_pusher{$table};
    for (@_)
    {
        $_ = "NULL" if (!defined $_);
    }
    push @$r, "(" . join(",", @_) . ")";
    if (@$r >= $db_pusher_count)
    {
        flush_push($table);
    }
}

sub flush_push
{
    my ($table) = (@_);
    my $r = $db_pusher{$table};
    if (@$r)
    {
        my $query = "INSERT INTO $table VALUES " . join(",", @$r);
        $query = encode("UTF8", $query);
        $dbh->do($query) or sys::err($dbh->errstr . " : " . $query);
        @$r = ();
    }
}

sub step_log
{
    my $p = $players->[$_[0]];
    my $r = $p->{tournament_log};
    for (@$r)
    {
        if ($_->{rated})
        {
            my $s = $_->{actual_score};
            if ($_->{oppestablished})
            {
                $p->{avg_opp_rating} = ($p->{avg_opp_rating}*$p->{estab_games} + $_->{opprating})/($p->{estab_games} + 1);
                $p->{estab_games}++;
            }
            
            if ($s eq "0")
            {
                $p->{losses}++;
            }
            elsif ($s == 0.5)
            {
                $p->{draws}++;
                $p->{estab_points} += 0.5 if ($_->{oppestablished});
            }
            elsif ($s == 1)
            {
                $p->{wins}++;
                $p->{estab_points} += 1.0 if ($_->{oppestablished});
            }
            else
            {
                warning("game id=" . $_->{game} . " has wrong result=" . $s);
                next;
            }
        }
        
        # calculation of established rating
        if ($_->{meestablished})
        {
            $_->{rating_diff} = $_->{k_factor}*($_->{actual_score}-$_->{expected_score});
            $p->{rating} += $_->{rating_diff};
        }
        else
        {
            $_->{rating_diff} = 0;
        }
       
        push_to_db($table_log,
            ++$log_id,
            $p->{id}, 
            $_->{opponent}, 
            $_->{opprating},
            $_->{game},
            $_->{tournament},
            qq("$_->{round}"),
            $_->{round_key},
            $_->{oppestablished},
            $_->{meestablished},
            $_->{expected_score},
            $_->{actual_score},
            $_->{k_factor});
    }
    
    if (@$r && !$r->[0]->{meestablished} && is_established($p))
    { # player became established, calculate performance rating
        my ($W, $L, $N) = ($p->{estab_points}, $p->{estab_games}-$p->{estab_points}, $p->{estab_games});
        $p->{rating} = $p->{avg_opp_rating} + (400*($W - $L))/$N;
        $p->{performance_string} = int($p->{avg_opp_rating}) . " + (400 * ($W - $L)) / $N";
        if ($p->{rating}-300 > $p->{avg_opp_rating})
        {
            $p->{rating} = $p->{avg_opp_rating} + 300;
            $p->{performance_string} = "*" . $p->{performance_string};
        }
    }
}

sub calculate_rating
{
    my ($me, $opponent, $result, $game, $rated) = (@_);

    my $log_entry = {};
    my $gid = $game->{id};
    $rated = 0 if (defined $unrated{$gid});
    $log_entry->{opponent} = $opponent->{id};
    $log_entry->{myrating} = $me->{rating};
    $log_entry->{opprating} = $opponent->{rating};
    $log_entry->{game} = $gid;
    $log_entry->{tournament} = $game->{tournament};
    $log_entry->{round} = $game->{round};
    $log_entry->{round_key} = $game->{round_key};
    $log_entry->{oppestablished} = is_established($opponent);
    $log_entry->{meestablished} = is_established($me);
    $log_entry->{expected_score} = 1/(1+10**(($opponent->{rating}-$me->{rating})/400));
    $log_entry->{actual_score} = $result;
    $log_entry->{k_factor} = $rated ? get_k_factor($me, $opponent) : 0;
    $log_entry->{rated} = $rated;
    
    if ($rated && $log_entry->{oppestablished})
    {
        $me->{g}++;
    }
    
    push @{$me->{tournament_log}}, $log_entry;
}

 
sub get_k_factor
{
    my ($me, $opponent) = (@_);
    if (is_established($me) && is_established($opponent))
    {
        return 32;
    }
    return 0;
}

sub is_established
{
    my ($player) = (@_);
    return 1 if ($player->{rif_updated});
    return 1 if ($player->{is_established});

    my $n_estab_games = $player->{estab_games};
    my $n_estab_points = $player->{estab_points};
    if ($n_estab_games >= 10 && $n_estab_points >= 3)
    {
        $player->{is_established} = 1;
        return 1;
    }
    return 0;
}

my $move = undef;

sub handle_start
{
    shift @_;
    my $token = shift @_;
    if ($token eq "move")
    {
        $move = "";
        return;
    }
    if (!defined $tokens{$token})
    {
        $tokens{$token} = [];
    }
    my $t = $tokens{$token};
    my %r = (@_);
    push @$t, \%r;
}

sub handle_char
{
    my ($p, $str) = @_;
    return unless defined $move;
    $move .= $str;
}

sub handle_end
{
    my $expat = shift @_;
    my $token = shift @_;
    if (defined $move)
    {
        $tokens{game}->[-1]->{move} = $move;
    }
    $move = undef;
}

sub warning
{
    my $text = join("", @_);
    open F, ">>parse_errors.log";
    print F "$text\n";
    close F;
}

END
{
    print qq(
   </pre>
   </html>
    );
    if (!$parse_ok)
    {
        unlink("flag.txt");
        sys::err("Input file parser error");
        exit;
    }
    unlink $input_file;
}
